package datamanagers;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.lang.time.StopWatch;

import commons.*;
import config.ConfigManager;

public class DataManagerMySql extends DataManager {

	private Connection connection;
	private StopWatch sw = new StopWatch();
	private HashMap<Integer, Float> mediaratingutenti;

	private void execute(String sql) throws SQLException {
		connection.prepareStatement(sql).execute();
	}

	public DataManagerMySql() throws DataManagerException {
		DatasourceMySql ds = new DatasourceMySql();
		try {
			this.connection = ds.getConnection();
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public void init() throws DataManagerException {

		try {
			PreparedStatement statement;
			String sql;
			/*
			 * execute("DROP INDEX RatingUserIdIndx ON tbl_ratings;");
			 * execute("DROP INDEX RatingProfileIdIndx ON tbl_ratings;");
			 * 
			 * // LOCK TABLES a WRITE; non serve.. loadRatings(); loadUsers();
			 * 
			 * if (ConfigManager.verbose)
			 * System.out.print("Aggiornamento indici, attendi...");
			 * 
			 * execute(
			 * "CREATE INDEX RatingUserIdIndx ON tbl_ratings (UserId) USING HASH;"
			 * ); execute(
			 * "CREATE INDEX RatingProfileIdIndx ON tbl_ratings (ProfileId) USING HASH;"
			 * );
			 * 
			 * if (ConfigManager.verbose) System.out.println("fatto!");
			 */
			mediaratingutenti = loadMappaMediaUser();

		} catch (Exception e) {
			throw new DataManagerException(e);
		}

	}

	public void close() throws DataManagerException {
		try {
			this.connection.close();
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public HashMap<Integer, Integer> getAllUsers() throws DataManagerException {
		try {
			// List<Integer> userlist = new LinkedList<Integer>();
			HashMap<Integer, Integer> userMap = new HashMap<Integer, Integer>();

			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			ratings_sql = "SELECT * FROM tbl_users";

			statement = connection.prepareStatement(ratings_sql);
			statement.execute();
			rs = statement.getResultSet();
			// int i =0;
			while (rs.next()) {
				// i++;
				int UserId = rs.getInt("UserId");
				// userlist.add(UserId);
				userMap.put(UserId, UserId);

			}
			statement.close();
			rs.close();
			// return userlist;
			return userMap;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public void dropUserSimilarityIndex() throws DataManagerException {
		try {
			execute("DROP INDEX RatingUserIdIndx ON tbl_ratings;");
			execute("DROP INDEX RatingProfileIdIndx ON tbl_ratings;");
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public void createUserSimilarityIndex() throws DataManagerException {
		try {
			execute("CREATE INDEX PSimilUserIdIndx ON tbl_pcc (UserId) USING HASH;");
			execute("CREATE INDEX PSimilProfileIdIndx ON tbl_pcc (ProfileId) USING HASH;");
		} catch (Exception e) {
			throw new DataManagerException(e);
		}

	}

	public void storeUserSimilarities(List<SimilarityItem> similarities)
			throws DataManagerException {
		try {
			String sql = "INSERT INTO tbl_similarity (UserId1,UserId2,Similarity,CommonRatings) VALUES ";
			String sep = "";
			PreparedStatement statement;
			for (SimilarityItem s : similarities) {
				String userSim = String.valueOf(
						Double.toString(s.commonratings)).replace(",", ".");

				sql += sep
						+ String.format("(%d,%d,%s,%d)", s.id1, s.id2, userSim,
								s.commonratings);
				sep = ",";
			}
			sql += ";";
			statement = connection.prepareStatement(sql);
			statement.executeUpdate();

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public UserRatingList getRatings(int UserId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			UserRatingList ratings = new UserRatingList();

			ratings_sql = "SELECT distinct ProfileId,Rating FROM tbl_ratings WHERE UserId=? ORDER BY ProfileID";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				UserRating rating = new UserRating();
				rating.UserId = rs.getInt("ProfileId");
				rating.Rating = rs.getInt("Rating");
				ratings.add(rating);
			}

			statement.close();
			rs.close();
			return ratings;

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public List<Integer> intersezioneRatingList(int UserId1, int UserId2)
			throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			// UserRatingList ratings = new UserRatingList();
			List<Integer> intersection = new LinkedList<Integer>();

			ratings_sql = "SELECT UserId FROM tbl_ratings "
					+ "INNER JOIN ( SELECT UserId FROM tbl_ratings  where ProfileId=? order by UserId ) as tbl "
					+ "USING (UserId) WHERE ProfileId=? ORDER BY UserId";
			// � molto lento come metodo
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId1);
			statement.setInt(2, UserId2);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				intersection.add(rs.getInt("UserId"));
			}

			statement.close();
			rs.close();
			return intersection;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}

	}

	public UserRatingList getScores(int UserId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			UserRatingList ratings = new UserRatingList();
			ratings_sql = "SELECT UserId,Rating FROM tbl_ratings WHERE ProfileId=? ORDER BY ProfileID";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				UserRating score = new UserRating();
				score.Rating = rs.getInt("Rating");
				score.UserId = rs.getInt("UserId");

				ratings.add(score);
			}

			statement.close();
			rs.close();
			return ratings;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public int getRating(int UserId, int ProfileId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			int rating = (Integer) null;
			String ratings_sql;

			ratings_sql = "SELECT * FROM tbl_ratings WHERE UserId=? AND ProfileId=?";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, ProfileId);
			statement.execute();
			rs = statement.getResultSet();

			if (rs.next()) {
				rating = rs.getInt("Rating");
			}
			statement.close();
			rs.close();
			return rating;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public void loadRatings() throws ConfigurationException, SQLException {

		String insert_ratings_sql;
		String ratings_path;
		ConfigManager config;
		File current_dir;
		PreparedStatement statement = null;

		if (ConfigManager.verbose)
			System.out.print("Inserimento dei rating nel db, attendi...");
		config = ConfigManager.getInstance();
		current_dir = new File("");
		ratings_path = current_dir.getAbsolutePath().toString()
				+ System.getProperty("file.separator")
				+ config.get("data.ratings");
		if (System.getProperty("file.separator").equals("\\"))
			ratings_path = ratings_path.replace("\\", "\\\\");

		insert_ratings_sql = String
				.format("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s.tbl_ratings FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@col1,@col2,@col3) SET UserID=@col1,ProfileID=@col2,Rating=@col3;",
						ratings_path, config.get("db.schema"));

		try {
			statement = this.connection.prepareStatement(insert_ratings_sql);
			statement.setQueryTimeout(60 * 10);
			statement.execute();
			if (ConfigManager.verbose)
				System.out.println("fatto!");
		} catch (SQLException e) {
			throw e;
		} finally {
			statement.close();
		}

	}

	public void loadUsers() throws ConfigurationException, SQLException {

		String insert_users_sql;
		String users_path;
		ConfigManager config;
		File current_dir;
		PreparedStatement statement = null;

		if (ConfigManager.verbose)
			System.out.print("Inserimento dei profili nel db, attendi...");
		config = ConfigManager.getInstance();
		current_dir = new File("");
		users_path = current_dir.getAbsolutePath().toString()
				+ System.getProperty("file.separator")
				+ config.get("data.ratings");
		if (System.getProperty("file.separator").equals("\\"))
			users_path = users_path.replace("\\", "\\\\");

		insert_users_sql = String
				.format("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s.tbl_users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@col1,@col2) SET UserID=@col1,Gender=@col2;",
						users_path, config.get("db.schema"));
		try {
			statement = this.connection.prepareStatement(insert_users_sql);
			statement.setQueryTimeout(60 * 10);
			statement.execute();
			if (ConfigManager.verbose)
				System.out.println("fatto!");
		} catch (SQLException e) {
			throw e;
		} finally {
			statement.close();
		}

	}

	public UserRatingMap getScoresMap(int UserId) throws DataManagerException {

		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			UserRatingMap mappa = new UserRatingMap();
			ratings_sql = "SELECT UserId,Rating FROM tbl_ratings WHERE ProfileId=? ORDER BY ProfileID";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				int userId = rs.getInt("UserId");
				double rating = rs.getDouble("Rating");
				mappa.add(userId, rating);
			}

			statement.close();
			rs.close();
			return mappa;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public UserRatingMap getRatingsMap(int UserId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			UserRatingMap mappa = new UserRatingMap();
			ratings_sql = "SELECT distinct ProfileId,Rating FROM tbl_ratings WHERE UserId=? ORDER BY ProfileID";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				int userId = rs.getInt("ProfileId");
				double rating = rs.getDouble("Rating");
				mappa.add(userId, rating);
			}
			statement.close();
			rs.close();
			return mappa;

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	/**
	 * Ritorna la mappa utenti e delle loro medie
	 * 
	 * @return HashMap<Integer,Float>
	 * @throws DataManagerException
	 */
	public HashMap<Integer, Float> getMappaMediaUser()
			throws DataManagerException {
		try {

			return mediaratingutenti;

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public HashMap<Integer, Float> getMappaMediaProfile()
			throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			HashMap<Integer, Float> mappa = new HashMap<Integer, Float>();
			ratings_sql = "SELECT ProfileId,media FROM tbl_P_var_med  ORDER BY ProfileId";
			statement = connection.prepareStatement(ratings_sql);
			statement.execute();
			rs = statement.getResultSet();
			while (rs.next()) {
				Integer userId = new Integer(rs.getInt("UserId"));
				Float media = new Float(rs.getFloat("media"));
				mappa.put(userId, media);
			}
			statement.close();
			rs.close();
			return mappa;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	private HashMap<Integer, Float> loadMappaMediaUser()
			throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			HashMap<Integer, Float> mappa = new HashMap<Integer, Float>();
			ratings_sql = "SELECT UserId,media FROM tbl_var_med  ORDER BY UserId";
			statement = connection.prepareStatement(ratings_sql);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				Integer userId = new Integer(rs.getInt("UserId"));
				Float media = new Float(rs.getFloat("media"));
				mappa.put(userId, media);
			}
			statement.close();
			rs.close();
			return mappa;

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	@Override
	public IntersezioneRating getIntersezioneRating(int UserId1, int UserId2)
			throws DataManagerException {
		try {
			int used = 0;
			int increment = 256;
			int size = increment;
			float[] ratings1 = new float[size];
			float[] ratings2 = new float[size];
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			ratings_sql = "SELECT t1.Rating as r1,t2.Rating as r2 FROM tbl_ratings_ridotto as t1 INNER JOIN tbl_ratings_ridotto as t2  WHERE t1.ProfileId=t2.ProfileId AND t1.UserId=? AND t2.UserId=?";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId1);
			statement.setInt(2, UserId2);
			statement.execute();
			rs = statement.getResultSet();

			int i = 0;
			while (rs.next()) {
				if (used == size) {
					float[] new_ratings1 = new float[size + increment];
					float[] new_ratings2 = new float[size + increment];
					System.arraycopy(ratings1, 0, new_ratings1, 0, used);
					System.arraycopy(ratings2, 0, new_ratings2, 0, used);
				}
				ratings1[i] = rs.getFloat("r1");
				ratings1[2] = rs.getFloat("r1");
				i++;
			}
			float[] new_ratings1 = new float[i];
			float[] new_ratings2 = new float[i];
			System.arraycopy(ratings1, 0, new_ratings1, 0, i);
			System.arraycopy(ratings2, 0, new_ratings2, 0, i);
			IntersezioneRating intersezione = new IntersezioneRating(ratings1,
					ratings2);
			return intersezione;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	@Override
	public float getMediaUser(int UserId) throws DataManagerException {
		return mediaratingutenti.get(UserId);
	}

	public void storeProfileSimilary(
			HashMap<Integer, SimilarityItem> similarities)
			throws DataManagerException {
		try {
			String sql = "INSERT INTO tbl_similarity (UserId,ProfileId,profileSimilarity) VALUES ";
			String sep = "";
			PreparedStatement statement;
			for (Integer app : similarities.keySet()) {
				SimilarityItem s = similarities.get(app);
				// String profileSim = String.valueOf(
				// Float.toString(s.profileSimilarity)).replace(",", ".");
				// String userSim = String.valueOf(
				// Float.toString(s.userSimilarity)).replace(",", ".");

				sql += sep
						+ String.format("(%d,%d,%f)", s.id1, s.id2,
								s.similarity);
				sep = ",";
			}
			sql += ";";
			statement = connection.prepareStatement(sql);
			statement.executeUpdate();

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

}